﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;

namespace NutriFast
{
    class dbVic
    {
         //Conection String
        public static string connString= connString = ConfigurationManager.ConnectionStrings["conn"].ConnectionString;


        //************************INICIO DE METODOS*************************************/

        //Metodo que busca el ID del ultimo paciente agregado

        public static int spVicBuscarIDSiguientePaciente() {
            int numero=0;

            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spVicBuscarIDSiguientePaciente", conn);
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(ds);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            try
            {
                numero = (int)ds.Tables[0].Rows[0]["idPatient"];

            }
            catch (IndexOutOfRangeException e)
            {
                Console.WriteLine(e.ToString());
            }
            conn.Close();
            return numero;
        }//Fin de buscar siguiente paciente


        //Funcion que inserta un paciente 
        public static bool insertarPaciente(Patient p)
        {

            //Variables locales
            bool insertado = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spVicInsertIntoPatient", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@pname", p.Pname);
            sqlCommand.Parameters.AddWithValue("@bd", p.Bd);
            sqlCommand.Parameters.AddWithValue("@sex", p.Sex);
            sqlCommand.Parameters.AddWithValue("@HW", p.Hw);
            sqlCommand.Parameters.AddWithValue("@CW", p.Cw);
            sqlCommand.Parameters.AddWithValue("@H", p.He);
            sqlCommand.Parameters.AddWithValue("@PCT", p.Pct);
            sqlCommand.Parameters.AddWithValue("@PCB", p.Pcb);
            sqlCommand.Parameters.AddWithValue("@PCSE", p.Pcse);
            sqlCommand.Parameters.AddWithValue("@Cbrazo", p.Cbrazo);
            sqlCommand.Parameters.AddWithValue("@Ccint", p.Ccint);
            sqlCommand.Parameters.AddWithValue("@Ccad", p.Ccad);
            sqlCommand.Parameters.AddWithValue("@Cabd", p.Cabd);
            sqlCommand.Parameters.AddWithValue("@Cmun", p.Cmun);
            sqlCommand.Parameters.AddWithValue("@Hcod", p.Hcod);
            sqlCommand.Parameters.AddWithValue("@Ccod", p.Ccod);
            sqlCommand.Parameters.AddWithValue("@PCSI", p.Pcsi);
            sqlCommand.Parameters.AddWithValue("@pass", p.Pass);

            try
            {
                sqlCommand.ExecuteNonQuery();
                insertado = true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }
            conn.Close();
            return insertado;
        }//Fin del insertar paciente



        //Funcion que elimina a un paciente 
        public static bool eliminarPaciente(int id)
        {

            //Variables locales
            bool borrado = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spVicDeletePatient", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@id", id);
          

            try
            {
                sqlCommand.ExecuteNonQuery();
                borrado = true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }
            conn.Close();
            return borrado;
        }//Fin del eliminar paciente



        //Funcion que actualiza un paciente
        public static bool actualizarPaciente(Patient p,int id)
        {

            //Variables locales
            bool insertado = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spVicUpdatePatient", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@id", id);
            sqlCommand.Parameters.AddWithValue("@pname", p.Pname);
            sqlCommand.Parameters.AddWithValue("@bd", p.Bd);
            sqlCommand.Parameters.AddWithValue("@sex", p.Sex);
            sqlCommand.Parameters.AddWithValue("@HW", p.Hw);
            sqlCommand.Parameters.AddWithValue("@CW", p.Cw);
            sqlCommand.Parameters.AddWithValue("@H", p.He);
            sqlCommand.Parameters.AddWithValue("@PCT", p.Pct);
            sqlCommand.Parameters.AddWithValue("@PCB", p.Pcb);
            sqlCommand.Parameters.AddWithValue("@PCSE", p.Pcse);
            sqlCommand.Parameters.AddWithValue("@Cbrazo", p.Cbrazo);
            sqlCommand.Parameters.AddWithValue("@Ccint", p.Ccint);
            sqlCommand.Parameters.AddWithValue("@Ccad", p.Ccad);
            sqlCommand.Parameters.AddWithValue("@Cabd", p.Cabd);
            sqlCommand.Parameters.AddWithValue("@Cmun", p.Cmun);
            sqlCommand.Parameters.AddWithValue("@Hcod", p.Hcod);
            sqlCommand.Parameters.AddWithValue("@Ccod", p.Ccod);
            sqlCommand.Parameters.AddWithValue("@PCSI", p.Pcsi);
            sqlCommand.Parameters.AddWithValue("@pass", p.Pass);

            try
            {
                sqlCommand.ExecuteNonQuery();
                insertado = true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }
            conn.Close();
            return insertado;
        }//Fin de actualizar paciente


        //Funcion que busca el último peso registrado para el paciente
        public static int getPeso(int id)
        {

            //Variables locales
            int peso = 0;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spVicSearchWeight", conn);
            sqlCommand.Parameters.AddWithValue("@id", id);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(ds);

           
                try
                {
                    peso = int.Parse(""+ds.Tables[0].Rows[0][0]);

                }
                catch (IndexOutOfRangeException e)
                {
                    Console.WriteLine(e.ToString());
                }

            conn.Close();
            return peso;
        }//Fin de obtener peso

        //Funcion que actualiza el peso de un paciente
        public static bool actualizarPeso(int id, float peso)
        {

            //Variables locales
            bool insertado = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spVicUpdateWeigth", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@id", id);
            sqlCommand.Parameters.AddWithValue("@current", peso);
            
            try
            {
                sqlCommand.ExecuteNonQuery();
                insertado = true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }
            conn.Close();
            return insertado;
        }//Fin de actualizar paciente


        //Funcion que crea un objeto paciente
        public static Patient crearPaciente(int id)
        {

            //Variables locales
            Patient pt=null;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spVicPatient", conn);
            sqlCommand.Parameters.AddWithValue("@id", id);

            sqlCommand.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(ds);


            try
            {
               
                pt = new Patient("" + ds.Tables[0].Rows[0]["pname"],
                    Convert.ToDateTime(ds.Tables[0].Rows[0]["BirthDate"]),
                    "" + ds.Tables[0].Rows[0]["Sex"],
                    float.Parse(""+ds.Tables[0].Rows[0][4]),
                    float.Parse(""+ds.Tables[0].Rows[0][5]),
                    float.Parse(""+ds.Tables[0].Rows[0][6]),
                    float.Parse(""+ds.Tables[0].Rows[0][7]),
                    float.Parse(""+ds.Tables[0].Rows[0][8]),
                    float.Parse(""+ds.Tables[0].Rows[0][9]),
                    float.Parse(""+ds.Tables[0].Rows[0][10]),
                    float.Parse(""+ds.Tables[0].Rows[0][11]),
                    float.Parse(""+ds.Tables[0].Rows[0][12]),
                    float.Parse(""+ds.Tables[0].Rows[0][13]),
                    float.Parse(""+ds.Tables[0].Rows[0][14]),
                    float.Parse(""+ds.Tables[0].Rows[0][15]),
                    "" + ds.Tables[0].Rows[0][16],
                    float.Parse(""+ds.Tables[0].Rows[0][17]),
                    float.Parse(""+ds.Tables[0].Rows[0][18]));
            }
            catch (IndexOutOfRangeException e)
            {
                Console.WriteLine(e.ToString());
            }

            conn.Close();
            return pt;
        }//Fin de crear persona


        //Funcion que inserta una dieta
        public static bool insertarDieta(Dieta dieta)
        {

            //Variables locales
            bool insertado = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spVicInsertIntoDiet", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@idPatient", dieta.IdPatient);
            sqlCommand.Parameters.AddWithValue("@date", DateTime.Now.Date);
            sqlCommand.Parameters.AddWithValue("@nmeals", dieta.Nmeals);
            sqlCommand.Parameters.AddWithValue("@weight", dieta.Weight);
            sqlCommand.Parameters.AddWithValue("@pt", dieta.Pt);
            sqlCommand.Parameters.AddWithValue("@lip", dieta.Lip);
            sqlCommand.Parameters.AddWithValue("@cho", dieta.Cho);
            sqlCommand.Parameters.AddWithValue("@kcalT",dieta.KcalT );
            sqlCommand.Parameters.AddWithValue("@geb", dieta.Geb);

            try
            {
                sqlCommand.ExecuteNonQuery();
                insertado = true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }
            conn.Close();
            return insertado;
        }//Fin del insertar dieta


        //Funcion que busca porcentajes
        public static float buscarPorcentajes(string macromineral, int dieta)
        {

            //Variables locales
            float lip=0, cho=0, proteinas=0,kcalT=0;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spVicBuscarPorcentajes", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@id", dieta);
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(ds);


            try
            {
               
                cho=float.Parse(""+ds.Tables[0].Rows[0][0]);
                proteinas=float.Parse(""+ds.Tables[0].Rows[0][1]);
                lip = float.Parse("" + ds.Tables[0].Rows[0][2]);
                kcalT = float.Parse("" + ds.Tables[0].Rows[0][3]);
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }
            conn.Close();
            if(macromineral=="proteinas")
                return proteinas;
            else if (macromineral == "lipidos")
                return lip;
            else if (macromineral == "cho")
                return cho;
            else
                return kcalT;
        }//Fin de buscar porcentajes

        //Funcion que busca ultima dieta
        public static int buscarDieta()
        {

            //Variables locales
            int dieta=0;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spVicBuscarDieta", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            DataSet ds = new DataSet();
            SqlDataAdapter da = new SqlDataAdapter(sqlCommand);
            da.Fill(ds);


            try
            {

                dieta = int.Parse("" + ds.Tables[0].Rows[0][0]);
                
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }
            conn.Close();
            return dieta;
        }//Fin de buscar dieta


        //Funcion que inserta una dieta
        public static bool actualizarDieta(Dieta dieta)
        {

            //Variables locales
            bool insertado = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spVicUpdateDiet", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@id", buscarDieta());
            sqlCommand.Parameters.AddWithValue("@idPatient", dieta.IdPatient);
            sqlCommand.Parameters.AddWithValue("@date", DateTime.Now.Date);
            sqlCommand.Parameters.AddWithValue("@nmeals", dieta.Nmeals);
            sqlCommand.Parameters.AddWithValue("@weight", dieta.Weight);
            sqlCommand.Parameters.AddWithValue("@pt", dieta.Pt);
            sqlCommand.Parameters.AddWithValue("@lip", dieta.Lip);
            sqlCommand.Parameters.AddWithValue("@cho", dieta.Cho);
            sqlCommand.Parameters.AddWithValue("@kcalT", dieta.KcalT);
            sqlCommand.Parameters.AddWithValue("@geb", dieta.Geb);

            try
            {
                sqlCommand.ExecuteNonQuery();
                insertado = true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }
            conn.Close();
            return insertado;
        }//Fin de actualizar dieta

        //Funcion que elimina a una dieta
        public static bool eliminarDieta(int id)
        {

            //Variables locales
            bool borrado = false;
            SqlConnection conn = new SqlConnection(connString);
            conn.Open();
            SqlCommand sqlCommand = new SqlCommand("spVicDeleteDieta", conn);
            sqlCommand.CommandType = CommandType.StoredProcedure;
            sqlCommand.Parameters.AddWithValue("@id", id);


            try
            {
                sqlCommand.ExecuteNonQuery();
                borrado = true;
            }
            catch (Exception e)
            {
                System.Console.WriteLine(e.StackTrace);
            }
            conn.Close();
            return borrado;
        }//Fin del eliminar dieta

    }
}
